-- @owner: @l3007kkk
-- @date: 2025/08/05
-- @testpoint:通过return语句查询rowcount的值

--连接D库
@conn PrimaryDbDmode;
--创建插件
--create extension shark;
--step1:创建表;expect:成功
drop table if exists t_rowcount0003;
create table t_rowcount0003(id int,name varchar(20));
insert into t_rowcount0003 values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
-- step2:创建自定义函数, 返回3;except:成功
drop function if exists func_rowcount0003(table_name TEXT);
CREATE OR REPLACE FUNCTION func_rowcount0003(table_name TEXT)
RETURNS TABLE (total_rows INT, affected_rows INT)
AS $$
DECLARE
row_count INT;
 affected_rows INT;
BEGIN
EXECUTE format('DELETE FROM %I WHERE id > 1', table_name);
SELECT @@ROWCOUNT INTO affected_rows;
EXECUTE format('SELECT COUNT(*) FROM %I', table_name) INTO row_count;
RETURN QUERY SELECT row_count, affected_rows;
END;
$$
LANGUAGE plpgsql;

-- step3:调用函数, row_count值为1 affected_rows值为2;except:成功
SELECT * FROM func_rowcount0003('t_rowcount0003');

--step4:清理环境;expect:成功
drop table if exists t_rowcount0003;
drop function if exists func_rowcount0003;
